﻿Imports System.Data
Imports System.Data.OleDb
Imports geraLaudosAutomatico.conexao
Imports System.Text

Public Class naturezaDAO


    Dim sql As New StringBuilder
    Dim objConn As New conexao
    Dim i As Integer
    Dim tipo_pericia As New tipo_periciaBO
    Dim natureza As New naturezaVO

    Public Function getNaturezas() As Generic.List(Of naturezaVO)

        sql.Clear()
        sql.Append("SELECT *FROM NATUREZA ORDER BY DESC_NATUREZA")

        Dim oReader As OleDbDataReader
        Dim lista As New Generic.List(Of naturezaVO)

        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oReader = oComando.ExecuteReader

            While oReader.Read

                natureza = New naturezaVO

                natureza.ID_NATUREZA = oReader("ID_NATUREZA")
                natureza.DESC_NATUREZA = oReader("DESC_NATUREZA")
                natureza.DENOMINACAO_OFICIAL = oReader("DENOMINACAO_OFICIAL")
                natureza.TIPO_PERICIA = tipo_pericia.getTipoPericia(oReader("ID_TIPO"))
                natureza.STATUS = oReader("STATUS")
                lista.Add(natureza)

            End While

            Return lista

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function



    Public Function getNatureza(ByVal id_natureza As Integer) As naturezaVO

        sql.Clear()
        sql.Append("SELECT *FROM NATUREZA WHERE ID_NATUREZA=" & id_natureza)

        Dim oReader As OleDbDataReader


        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oReader = oComando.ExecuteReader

            While oReader.Read

                natureza = New naturezaVO
                natureza.ID_NATUREZA = oReader("ID_NATUREZA")
                natureza.DESC_NATUREZA = oReader("DESC_NATUREZA")
                natureza.DENOMINACAO_OFICIAL = oReader("DENOMINACAO_OFICIAL")
                natureza.TIPO_PERICIA = tipo_pericia.getTipoPericia(oReader("ID_TIPO"))
                natureza.STATUS = oReader("STATUS")


            End While

            Return natureza

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function
    ''' <summary>
    ''' Exibe as naturezas de acordo com o tipo de pericia selecionada"
    ''' </summary>
    ''' <param name="id_tipo_pericia">ID_TIPO  é a chave estrangeira da tabela tipo_pericia</param>
    Public Function getTipoNatureza(ByVal id_tipo_pericia As Integer) As Generic.List(Of naturezaVO)

        sql.Clear()
        sql.Append("SELECT *FROM NATUREZA WHERE ID_TIPO=@ID_TIPO AND STATUS=TRUE ORDER BY DESC_NATUREZA")

        Dim oReader As OleDbDataReader
        Dim lista As New Generic.List(Of naturezaVO)
      

        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oComando.Parameters.AddWithValue("@ID_TIPO", id_tipo_pericia)

            oReader = oComando.ExecuteReader

            While oReader.Read

                natureza = New naturezaVO
                natureza.ID_NATUREZA = oReader("ID_NATUREZA")
                natureza.DESC_NATUREZA = oReader("DESC_NATUREZA")
                natureza.DENOMINACAO_OFICIAL = oReader("DENOMINACAO_OFICIAL")
                natureza.STATUS = oReader("STATUS")
                lista.Add(natureza)


            End While

            Return lista

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function

    ''' <summary>
    ''' Exibe as naturezas de acordo com o tipo de pericia selecionada"
    ''' </summary>
    ''' <param name="id_natureza">ID_TIPO  é a chave estrangeira da tabela tipo_pericia</param>
    Public Function getNaturezaCompleta(ByVal id_natureza As Integer) As naturezaVO

        sql.Clear()
        sql.Append(" SELECT *FROM tipo_pericia INNER JOIN natureza ON tipo_pericia.id_tipo = natureza.id_tipo WHERE NATUREZA.ID_NATUREZA=@ID_NATUREZA")

        Dim oReader As OleDbDataReader

        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oComando.Parameters.AddWithValue("@ID_NATUREZA", id_natureza)

            oReader = oComando.ExecuteReader

            While oReader.Read

                natureza = New naturezaVO
                natureza.ID_NATUREZA = IIf(IsDBNull(oReader("ID_NATUREZA")), Nothing, oReader("ID_NATUREZA"))
                natureza.DESC_NATUREZA = IIf(IsDBNull(oReader("DESC_NATUREZA")), Nothing, oReader("DESC_NATUREZA"))
                natureza.DENOMINACAO_OFICIAL = IIf(IsDBNull(oReader("DENOMINACAO_OFICIAL")), Nothing, oReader("DENOMINACAO_OFICIAL"))
                natureza.STATUS = IIf(IsDBNull(oReader("NATUREZA.STATUS")), Nothing, oReader("NATUREZA.STATUS"))
                natureza.TIPO_PERICIA = IIf(IsDBNull(oReader("NATUREZA.ID_TIPO")), Nothing, New tipo_periciaVO With {.ID_TIPO = oReader("TIPO_PERICIA.ID_TIPO"), .DESC_TIPO = oReader("DESC_TIPO"), .STATUS = oReader("TIPO_PERICIA.STATUS"), .PRAZO = oReader("PRAZO")})
                natureza.PONTOS_ATENDIMENTO = IIf(IsDBNull(oReader("PONTOS_ATENDIMENTO")), Nothing, oReader("PONTOS_ATENDIMENTO"))
                natureza.PONTOS_LAUDO = IIf(IsDBNull(oReader("PONTOS_LAUDO")), Nothing, oReader("PONTOS_LAUDO"))
                natureza.DESLOCAMENTO = IIf(IsDBNull(oReader("DESLOCAMENTO")), Nothing, oReader("DESLOCAMENTO"))

            End While

            Return natureza

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function
End Class
